from sqlalchemy import create_engine
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'ssql'
USERNAME ='root'
PASSWORD = 'root'

#engine=create_engine('mysql+pymysql://root:qwe123@localhost:3306/test',echo=True)
DB_URI = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
print(DB_URI)
engine = create_engine(DB_URI)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(engine)

from sqlalchemy import Column,Integer,String,Text,Boolean,Date,DateTime,Float,ForeignKey
from datetime import datetime,date
from sqlalchemy.orm import relationship

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer,primary_key=True,autoincrement=True)
    d_name = Column(String(100))

    def __repr__(self):
        return '<Department(id="%s",d_name="%s")>' % (self.id, self.d_name)


class Student(Base):
    __tablename__ = 'student'

    s_id = Column(Integer, primary_key=True, autoincrement=True)
    s_name = Column(String(100))
    d_id = Column(Integer, ForeignKey('department.id'))

    department = relationship('Department', backref='student')
    stu_details = relationship('Stu_details', uselist=False)

    def __repr__(self):
        return '<Student(s_id="%s",d_name="%s,d_id=%s")>' % (self.s_id, self.s_name, self.d_id)

class Stu_details(Base):
	__tablename__ = 'stu_details'

	id = Column(Integer,primary_key=True)
	age = Column(Integer)
	city = Column(String(100))
	s_id = Column(Integer,ForeignKey('student.s_id'))

	student = relationship('Student')

    # def __repr__(self):
    #     return '<Stu_details(id="%s",age="%s,city="%s",s_id="%s")>' % (self.id, self.age, self.city,self.s_id)


Base.metadata.create_all()

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(engine)
session = Session()

# session.add(Department(d_name='AAAA'))
# session.commit()
# session.add(Student(s_name='haha',d_id=1))
# session.commit()

# rs = session.query(Department).first()
# s1 = Student(s_name='meimei')
# rs.student.append(s1)
# session.commit()
# print(rs)
# print(rs.student)
rs = session.query(Student).first()
print(rs.stu_details)



